﻿//************************************************************************************************************
//文件名称(File Name)：MarketingListMana.cs
//功能描述(Description)：营销查询及效果评估管理类
//数据表(Tables)：
//作者(Author)：
//日期(Create Date)：20130302
//参考文档(Reference)(可选)：
//引用(Using) (可选)﹕
//************************************************************************************************************

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Globalization;
using System.Data;
using System.Data.SqlClient;
using DbAdapter.sqldbAdapter;

namespace AnalyseCrmMana
{
    public class MarketingListMana
    {
        /// <summary>
        /// 清单批次号
        /// </summary>
        public string listpcno
        {
            get;
            set;
        }
        /// <summary>
        /// 营销活动名称
        /// </summary>
        public string marketingname
        {
            get;
            set;
        }
        /// <summary>
        /// 营销周期
        /// </summary>
        public string marketingperiod
        {
            get;
            set;
        }
        /// <summary>
        /// 名单生成日期
        /// </summary>
        public string namelistdt
        {
            get;
            set;
        }
        /// <summary>
        /// 清单组合
        /// </summary>
        public string listcombination
        {
            get;
            set;
        }
        /// <summary>
        /// 客户号
        /// </summary>
        public string guestno
        {
            get;
            set;
        }
        /// <summary>
        /// 客户渠道
        /// </summary>
        public string customerchannel
        {
            get;
            set;
        }
        /// <summary>
        /// 营业部
        /// </summary>
        public string salesdepartment
        {
            get;
            set;
        }
        /// <summary>
        /// 联系电话
        /// </summary>
        public string contactnumber
        {
            get;
            set;
        }
        /// <summary>
        /// 联系地址
        /// </summary>
        public string contactaddress
        {
            get;
            set;
        }

        private string savesql;

        public string _savesql
        {
            get {
                return savesql;
            }
            set
            {
                savesql = value;
            }
        }
        /// <summary>
        /// 查询营销客户 IList<MarketingListMana>
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns> 
        public IList<MarketingListMana> getMarketingList(string param,string curuser)
        {
            string selcom = queryconditiongroup(param);
            string psavesql = "";
            string[] strparam = param.Split('|');
            int arri = strparam.Length;
            int startrow = Convert.ToInt32(strparam[arri-1]) * (Convert.ToInt32(strparam[arri-2]) - 1);
            int endrow = Convert.ToInt32(strparam[arri-1]) * Convert.ToInt32(strparam[arri-2]) + 1;
            string curyy = DateTime.Now.Year.ToString();
            string curmm = DateTime.Now.Month.ToString();
            string yymmdd = "";
            StringBuilder sqlbul = new StringBuilder();
            string listcom = formatqueryconditiongroup(selcom);
            string pihao = curuser+"_" + DateTime.Now.ToString("yyyyMMddHHmm", DateTimeFormatInfo.InvariantInfo); 
            sqlbul.Append("SELECT H.* FROM (SELECT ROW_NUMBER() OVER(ORDER BY G.guestno DESC) rownumber,G.* FROM (select '" + pihao + "' listpcno,'" + listcom + "' listcombination,"
                + "A.guestno,A.guestname,A.CHANNEL,A.SALESDEPARTMENT,A.RELTELPHONE,A.RELADDRESS"); //from Tbt_GuestInfo A
            if (selcom.IndexOf('1') != -1)
            {
                sqlbul.Append(",A.ages,A.opendays,A.guesttype");
            }
            if (selcom.IndexOf('2') != -1)
            {
                sqlbul.Append(",B.rijunzich1,B.rijunzich2,B.rijunzich3,B.rijunzich4,B.gusuanzich");
                sqlbul.Append(",C.jingzichtr1,C.jingzichtr2,C.jingzichtr3,C.jingzichtr4,C.shymchichang");
                sqlbul.Append(",C.wtmrzztbb,C.huanshoulv1,C.huanshoulv2,C.huanshoulv3,C.huanshoulv4,C.yingkuilv1,C.yingkuilv2");
                sqlbul.Append(",C.yingkuilv3,C.yingkuilv4,C.xingshengou");
            }
            if (selcom.IndexOf('3') != -1)
            {
                sqlbul.Append(",population1,population2");
            }
            if (selcom.IndexOf('4') != -1)
            {
                sqlbul.Append(",gpxjjmx,jjrgmx,hhxjjmx,jjsgmx,jjhbmx,jjmrmx");
            }
            //增加表
            sqlbul.Append(" from V_GuestInfo A");
            if (selcom.IndexOf('2') != -1)
            {
                sqlbul.Append(" inner join V_GuestProperty B ON A.guestno = B.GUESTNO AND B.COUNTMM ='"+curyy+"'");
                if (curmm == "1")
                {
                    curyy = (DateTime.Now.Year - 1).ToString();
                    yymmdd = curyy + "1201";
                }
                else
                {
                    curmm = ((Convert.ToInt32(curmm) - 1).ToString().Length == 1)
                        ? ("0" + (Convert.ToInt32(curmm) - 1).ToString()) : (Convert.ToInt32(curmm) - 1).ToString();
                    yymmdd = curyy + curmm + "01";
                }
                sqlbul.Append(" inner join V_PaDataSample C ON A.guestno = C.SRC_CUST_NO and C.STAT_DATE = '" + yymmdd + "'");
            }
            if (selcom.IndexOf('3') != -1)
            {
                sqlbul.Append(" inner join Tbt_CustomerClustering D ON A.guestno = D.GUESTNO");
            }
            if (selcom.IndexOf('4') != -1)
            {
                if (strparam[0] == "1")  //用于本月营销
                {
                    string cyymm = "";
                    if (curmm == "1")
                    {
                        curyy = (DateTime.Now.Year - 1).ToString();
                        cyymm = curyy + "11";
		            }
                    else if (curmm == "2")
                    {
                        curyy = (DateTime.Now.Year - 1).ToString();
                        cyymm = curyy + "12";
                    }
                    else
                    {
                        cyymm = curyy + (((Convert.ToInt32(curmm) - 2).ToString().Length == 1)
                            ? ("0" + (Convert.ToInt32(curmm) - 2).ToString()) : (Convert.ToInt32(curmm) - 2).ToString());
                    }
                    sqlbul.Append(" inner join V_GuestJJModel E ON A.guestno = E.GUESTNO and E.COUNTMM = '" + cyymm + "'");
                }
                else if (strparam[0] == "2")  //用于下月营销
                {
                    string cyymm = "";
                    if (curmm == "1")
                    {
                        curyy = (DateTime.Now.Year - 1).ToString();
                        cyymm = curyy + "12";
                    }                    
                    else
                    {
                        cyymm = curyy + (((Convert.ToInt32(curmm) - 1).ToString().Length == 1)
                            ? ("0" + (Convert.ToInt32(curmm) - 1).ToString()) : (Convert.ToInt32(curmm) - 1).ToString());
                    }
                    sqlbul.Append(" inner join V_GuestJJModel E ON A.guestno = E.GUESTNO and E.COUNTMM = '" + cyymm + "'");
                }
                else if (strparam[0] == "3")  //用于本月营销&用于下月营销
                {
                    string cyymm = "";
                    string nyymm = "";
                    if (curmm == "1")
                    {
                        curyy = (DateTime.Now.Year - 1).ToString();
                        cyymm = curyy + "11";
                        nyymm = curyy + "12";
                    }
                    else if (curmm == "2")
                    {
                        curyy = (DateTime.Now.Year - 1).ToString();
                        cyymm = curyy + "12";
                        nyymm = DateTime.Now.Year.ToString() + "01";
                    }
                    else
                    {
                        cyymm = curyy + (((Convert.ToInt32(curmm) - 2).ToString().Length == 1)
                            ? ("0" + (Convert.ToInt32(curmm) - 2).ToString()) : (Convert.ToInt32(curmm) - 2).ToString());
                        nyymm = curyy + (((Convert.ToInt32(curmm) - 1).ToString().Length == 1)
                            ? ("0" + (Convert.ToInt32(curmm) - 1).ToString()) : (Convert.ToInt32(curmm) - 1).ToString());
                    }
                    sqlbul.Append(" inner join V_GuestJJModel E ON A.guestno = E.GUESTNO and (E.COUNTMM = '"
                        + cyymm + "') or (E.COUNTMM = '" + nyymm + ") ");
                }
            }
            if ((strparam[0] != "1") | (strparam[0] != "2") | (strparam[0] != "3"))
            {
                sqlbul.Append(") G where " + strparam[0] + ") H ");//"WHERE H.rownumber > " + startrow + " and H.rownumber < " + endrow);
                psavesql = sqlbul.ToString();
                sqlbul.Append("WHERE H.rownumber > " + startrow + " and H.rownumber < " + endrow);
            }
            else
            {
                sqlbul.Append(") G where " + strparam[1] + ") H ");//WHERE H.rownumber > " + startrow + " and H.rownumber < " + endrow);
                psavesql = sqlbul.ToString();
                sqlbul.Append("WHERE H.rownumber > " + startrow + " and H.rownumber < " + endrow);
            }
            sqldb db = new sqldb();
            DataSet ds = db.ExecuteDataset(sqlconn.GetConnStr(), CommandType.Text, sqlbul.ToString());
            IList<MarketingListMana> marklist = null;
            if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
            {
                marklist = new List<MarketingListMana>();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    MarketingListMana markmana = new MarketingListMana();
                    markmana.listpcno = dr["listpcno"].ToString();
                    markmana.marketingname = "";
                    markmana.marketingperiod = "";
                    markmana.namelistdt = "";
                    markmana.listcombination = dr["listcombination"].ToString();
                    markmana.guestno = dr["guestno"].ToString();
                    markmana.customerchannel = dr["CHANNEL"].ToString();
                    markmana.salesdepartment = dr["SALESDEPARTMENT"].ToString();
                    markmana.contactnumber = dr["RELTELPHONE"].ToString();
                    markmana.contactaddress = dr["RELADDRESS"].ToString();
                    markmana.savesql = psavesql;
                    marklist.Add(markmana);
                }
            }
            return marklist;
        }

        /// <summary>
        /// 获取营销客户的数量
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public int getMarketingCount(string param)
        {
            string selcom = queryconditiongroup(param);
            string[] strparam = param.Split('|');
            string curyy = DateTime.Now.Year.ToString();
            string curmm = DateTime.Now.Month.ToString();
            string yymmdd = "";
            StringBuilder sqlbul = new StringBuilder();
            string listcom = formatqueryconditiongroup(selcom);
            string pihao = "zhangsan_" + DateTime.Now.ToString("yyyyMMddHHmmss", DateTimeFormatInfo.InvariantInfo);
            sqlbul.Append("SELECT count(1) FROM (select '" + pihao + "' listpcno,'" + listcom + "' listcombination,"
                + "A.guestno,A.guestname,A.CHANNEL,A.SALESDEPARTMENT,A.RELTELPHONE,A.RELADDRESS"); //from Tbt_GuestInfo A
            if (selcom.IndexOf('1') != -1)
            {
                sqlbul.Append(",A.ages,A.opendays,A.guesttype");
            }
            if (selcom.IndexOf('2') != -1)
            {
                sqlbul.Append(",B.rijunzich1,B.rijunzich2,B.rijunzich3,B.rijunzich4,B.gusuanzich");
                sqlbul.Append(",C.jingzichtr1,C.jingzichtr2,C.jingzichtr3,C.jingzichtr4,C.shymchichang");
                sqlbul.Append(",C.wtmrzztbb,C.huanshoulv1,C.huanshoulv2,C.huanshoulv3,C.huanshoulv4,C.yingkuilv1,C.yingkuilv2");
                sqlbul.Append(",C.yingkuilv3,C.yingkuilv4,C.xingshengou");
            }
            if (selcom.IndexOf('3') != -1)
            {
                sqlbul.Append(",population1,population2");
            }
            if (selcom.IndexOf('4') != -1)
            {
                sqlbul.Append(",gpxjjmx,jjrgmx,hhxjjmx,jjsgmx,jjhbmx,jjmrmx");
            }
            //增加表
            sqlbul.Append(" from V_GuestInfo A");
            if (selcom.IndexOf('2') != -1)
            {
                sqlbul.Append(" inner join V_GuestProperty B ON A.guestno = B.GUESTNO AND B.COUNTMM ='" + curyy + "'");
                if (curmm == "1")
                {
                    curyy = (DateTime.Now.Year - 1).ToString();
                    yymmdd = curyy + "1201";
                }
                else
                {
                    curmm = ((Convert.ToInt32(curmm) - 1).ToString().Length == 1)
                        ? ("0" + (Convert.ToInt32(curmm) - 1).ToString()) : (Convert.ToInt32(curmm) - 1).ToString();
                    yymmdd = curyy + curmm + "01";
                }
                sqlbul.Append(" inner join V_PaDataSample C ON A.guestno = C.SRC_CUST_NO and C.STAT_DATE = '" + yymmdd + "'");
            }
            if (selcom.IndexOf('3') != -1)
            {
                sqlbul.Append(" inner join Tbt_CustomerClustering D ON A.guestno = D.GUESTNO");
            }
            if (selcom.IndexOf('4') != -1)
            {
                if (strparam[0] == "1")  //用于本月营销
                {
                    string cyymm = "";
                    if (curmm == "1")
                    {
                        curyy = (DateTime.Now.Year - 1).ToString();
                        cyymm = curyy + "11";
                    }
                    else if (curmm == "2")
                    {
                        curyy = (DateTime.Now.Year - 1).ToString();
                        cyymm = curyy + "12";
                    }
                    else
                    {
                        cyymm = curyy + (((Convert.ToInt32(curmm) - 2).ToString().Length == 1)
                            ? ("0" + (Convert.ToInt32(curmm) - 2).ToString()) : (Convert.ToInt32(curmm) - 2).ToString());
                    }
                    sqlbul.Append(" inner join V_GuestJJModel E ON A.guestno = E.GUESTNO and E.COUNTMM = '" + cyymm + "'");
                }
                else if (strparam[0] == "2")  //用于下月营销
                {
                    string cyymm = "";
                    if (curmm == "1")
                    {
                        curyy = (DateTime.Now.Year - 1).ToString();
                        cyymm = curyy + "12";
                    }
                    else
                    {
                        cyymm = curyy + (((Convert.ToInt32(curmm) - 1).ToString().Length == 1)
                            ? ("0" + (Convert.ToInt32(curmm) - 1).ToString()) : (Convert.ToInt32(curmm) - 1).ToString());
                    }
                    sqlbul.Append(" inner join V_GuestJJModel E ON A.guestno = E.GUESTNO and E.COUNTMM = '" + cyymm + "'");
                }
                else if (strparam[0] == "3")  //用于本月营销&用于下月营销
                {
                    string cyymm = "";
                    string nyymm = "";
                    if (curmm == "1")
                    {
                        curyy = (DateTime.Now.Year - 1).ToString();
                        cyymm = curyy + "11";
                        nyymm = curyy + "12";
                    }
                    else if (curmm == "2")
                    {
                        curyy = (DateTime.Now.Year - 1).ToString();
                        cyymm = curyy + "12";
                        nyymm = DateTime.Now.Year.ToString() + "01";
                    }
                    else
                    {
                        cyymm = curyy + (((Convert.ToInt32(curmm) - 2).ToString().Length == 1)
                            ? ("0" + (Convert.ToInt32(curmm) - 2).ToString()) : (Convert.ToInt32(curmm) - 2).ToString());
                        nyymm = curyy + (((Convert.ToInt32(curmm) - 1).ToString().Length == 1)
                            ? ("0" + (Convert.ToInt32(curmm) - 1).ToString()) : (Convert.ToInt32(curmm) - 1).ToString());
                    }
                    sqlbul.Append(" inner join V_GuestJJModel E ON A.guestno = E.GUESTNO and (E.COUNTMM = '"
                        + cyymm + "') or (E.COUNTMM = '" + nyymm + ") ");
                }
            }
            if ((strparam[0] != "1") | (strparam[0] != "2") | (strparam[0] != "3"))
            {
                sqlbul.Append(") G where " + strparam[0]);
            }
            else
            {
                sqlbul.Append(") G where " + strparam[1]);
            }
            sqldb db = new sqldb();
            object rowcount = db.ExecuteScalar(sqlconn.GetConnStr(), CommandType.Text, sqlbul.ToString());
            return Convert.ToInt32(rowcount);
        }

        /// <summary>
        /// 根据查询条件判断使用那些表 1 基本特征 2 客户视图 3 客户分群 4 基金认购模型
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public string queryconditiongroup(string query)
        {
            string result = ""; 
            if ((query.IndexOf("ages") != -1) | (query.IndexOf("opendays") != -1) | (query.IndexOf("guesttype") != -1))
            {
                result += "1|";
            }
            if ((query.IndexOf("rijunzich") != -1) | (query.IndexOf("yongjin") != -1)
                    | (query.IndexOf("jingzichtr") != -1) | (query.IndexOf("gusuanzich") != -1)
                    | (query.IndexOf("shymchichang") != -1) | (query.IndexOf("wtmrzztbb") != -1)
                    | (query.IndexOf("huanshoulv") != -1) | (query.IndexOf("yingkuilv") != -1)
                    | (query.IndexOf("xingshengou") != -1))
            {
                result += "2|";
            }
            if ((query.IndexOf("population1") != -1) | (query.IndexOf("population2") != -1))
            {
                result += "3|";
            }
            if ((query.IndexOf("countmm") != -1) | (query.IndexOf("gpxjjmx") != -1)
                    | (query.IndexOf("jjrgmx") != -1) | (query.IndexOf("hhxjjmx") != -1)
                    | (query.IndexOf("jjsgmx") != -1) | (query.IndexOf("jjhbmx") != -1)
                    | (query.IndexOf("jjmrmx") != -1))
            {
                result += "4|";
            }
            return result.Substring(0,result.Length-1);
        }

        public string formatqueryconditiongroup(string querygroup)
        {
            string[] queryformat = querygroup.Split('|');
            StringBuilder querybul = new StringBuilder();
            foreach (string query in queryformat)
            {
                switch (query)
                {
                    case "1":
                        querybul.Append("基本特征+");
                        break;
                    case "2":
                        querybul.Append("客户视图+");
                        break;
                    case "3":
                        querybul.Append("客户分群+");
                        break;
                    case "4":
                        querybul.Append("基金认购模型+");
                        break;
                    default:
                        break;
                }
            }
            string resultstr = querybul.ToString();
            return resultstr.Substring(0, resultstr.Length - 1);
        }

        /// <summary>
        /// 保存到永久表中
        /// </summary>
        /// <param name="prParam"></param>
        /// <returns></returns>
        public int SaveMarketingFound(string[] prParam)
        {
            int rowcount = 0;
            string sql = "select * from Tbt_TempMarketingFound";
            sqldb db = new sqldb();
            DataSet ds = db.ExecuteDataset(sqlconn.GetConnStr(), CommandType.Text, sql);
            if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    MarketingListMana markmana = new MarketingListMana();
                    markmana.listpcno = dr["LISTPCNO"].ToString();
                    markmana.marketingname = prParam[0];
                    markmana.marketingperiod = prParam[1];
                    markmana.namelistdt = DateTime.Now.Date.ToString("yyyyMMdd", DateTimeFormatInfo.InvariantInfo);
                    markmana.listcombination = dr["LISTCOMBINATION"].ToString();
                    markmana.guestno = dr["GUESTNO"].ToString();
                    markmana.customerchannel = dr["CUSTOMERCHANNEL"].ToString();
                    markmana.salesdepartment = dr["SALESDEPARTMENT"].ToString();
                    markmana.contactnumber = dr["CONTACTNUMBER"].ToString();
                    markmana.contactaddress = dr["CONTACTADDRESS"].ToString();
                    if (AddNewMarketingFound(markmana) > 0)
                    {
                        rowcount++;
                    }
                }
                CommonMana cmana = new CommonMana();
                cmana.AddNewDownlistItem(prParam[0], "营销活动名称");
            }
            return rowcount;
        }

        /// <summary>
        /// 添加新的市场营销查询结果
        /// </summary>
        /// <param name="markmana"></param>
        /// <returns></returns>
        private int AddNewMarketingFound(MarketingListMana markmana)
        {
            string sql = "insert into Tbt_FoundMarketingList(LISTPCNO,MARKETINGNAME,MARKETINGPERIOD,NAMELISTDT,LISTCOMBINATION,GUESTNO,CUSTOMERCHANNEL,SALESDEPARTMENT,"
                        + "CONTACTNUMBER,CONTACTADDRESS) values('" + markmana.listpcno + "','" + markmana.marketingname + "','"
                        + markmana.marketingperiod + "','" + markmana.namelistdt + "','" + markmana.listcombination + "','"
                        + markmana.guestno + "','" + markmana.customerchannel + "','" + markmana.salesdepartment + "','"
                        + markmana.contactnumber + "','" + markmana.contactaddress + "')";
            sqldb db = new sqldb();
            return db.ExecuteNonQuery(sqlconn.GetConnStr(), CommandType.Text, sql);            
        }

        /// <summary>
        /// 保存查询结果的临时数据
        /// </summary>
        /// <param name="prParam"></param>
        /// <returns></returns>
        public int SaveTempMarketingFound(string prParam)
        {
            int rowcount = 0;
            sqldb db = new sqldb();
            DataSet ds = db.ExecuteDataset(sqlconn.GetConnStr(), CommandType.Text, prParam);
            if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
            {
                TruncateTempFound();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    MarketingListMana markmana = new MarketingListMana();
                    markmana.listpcno = dr["listpcno"].ToString();
                    markmana.listcombination = dr["listcombination"].ToString();
                    markmana.guestno = dr["guestno"].ToString();
                    markmana.customerchannel = dr["CHANNEL"].ToString();
                    markmana.salesdepartment = dr["SALESDEPARTMENT"].ToString();
                    markmana.contactnumber = dr["RELTELPHONE"].ToString();
                    markmana.contactaddress = dr["RELADDRESS"].ToString();
                    if (AddNewTempMarketingFound(markmana) > 0)
                    {
                        rowcount++;
                    }
                }
            }
            return rowcount;
        }

        public int builderformarketingfound(string user)
        {
            string sql = "execute Proc_MarketingFound '" + user + "'";
            sqldb db = new sqldb();
            return db.ExecuteNonQuery(sqlconn.GetConnStr(), CommandType.Text, sql);
        }
        /// <summary>
        /// 添加市场营销查询结果到临时表中
        /// </summary>
        /// <param name="markmana"></param>
        /// <returns></returns>
        private int AddNewTempMarketingFound(MarketingListMana markmana)
        {
            string sql = "insert into Tbt_TempMarketingFound(LISTPCNO,LISTCOMBINATION,GUESTNO,CUSTOMERCHANNEL,SALESDEPARTMENT,"
                        + "CONTACTNUMBER,CONTACTADDRESS) values('" + markmana.listpcno + "','" + markmana.listcombination + "','"
                        + markmana.guestno + "','" + markmana.customerchannel + "','" + markmana.salesdepartment + "','"
                        + markmana.contactnumber + "','" + markmana.contactaddress + "')";
            sqldb db = new sqldb();
            return db.ExecuteNonQuery(sqlconn.GetConnStr(), CommandType.Text, sql);
        }

        private int TruncateTempFound()
        {
            string sql = "truncate table Tbt_TempMarketingFound";
            sqldb db = new sqldb();
            return db.ExecuteNonQuery(sqlconn.GetConnStr(), CommandType.Text, sql);
        }

        /// <summary>
        /// 根据活动名称导出市场营销的查询结果
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public IList<MarketingListMana> GetAllMarketingList(string param)
        {
            string[] parastr = param.Split('|');
            string sql = "select LISTPCNO,MARKETINGNAME,MARKETINGPERIOD,NAMELISTDT,LISTCOMBINATION,GUESTNO,"
                        + "CUSTOMERCHANNEL,SALESDEPARTMENT,CONTACTNUMBER,CONTACTADDRESS from Tbt_FoundMarketingList "
                        + "where MARKETINGNAME = '" + parastr[0] + "' and MARKETINGPERIOD = '" + parastr[1] + "'";
            sqldb db = new sqldb();
            DataSet ds = db.ExecuteDataset(sqlconn.GetConnStr(), CommandType.Text, sql.ToString());
            IList<MarketingListMana> marklist = null;
            if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
            {
                marklist = new List<MarketingListMana>();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    MarketingListMana markmana = new MarketingListMana();
                    markmana.listpcno = dr["LISTPCNO"].ToString();
                    markmana.marketingname = dr["MARKETINGNAME"].ToString();
                    markmana.marketingperiod = dr["MARKETINGPERIOD"].ToString();
                    markmana.namelistdt = dr["NAMELISTDT"].ToString();
                    markmana.listcombination = dr["LISTCOMBINATION"].ToString();
                    markmana.guestno = dr["GUESTNO"].ToString();
                    markmana.customerchannel = dr["CUSTOMERCHANNEL"].ToString();
                    markmana.salesdepartment = dr["SALESDEPARTMENT"].ToString();
                    markmana.contactnumber = dr["CONTACTNUMBER"].ToString();
                    markmana.contactaddress = dr["CONTACTADDRESS"].ToString();
                    markmana.savesql = "";
                    marklist.Add(markmana);
                }
            }
            return marklist;
        }     
    }

    /// <summary>
    /// 营销反馈清单
    /// </summary>
    public class MarketingFeedbackMana
    {
        /// <summary>
        /// 清单批次号
        /// </summary>
        public string listpcno
        {
            get;
            set;
        }
        /// <summary>
        /// 营销活动名称	
        /// </summary>
        public string marketingname
        {
            get;
            set;
        }

        /// <summary>
        /// 营销周期	
        /// </summary>
        public string marketingperiod
        {
            get;
            set;
        }
        /// <summary>
        /// 客户号	
        /// </summary>
        public string guestno
        {
            get;
            set;
        }
        /// <summary>
        /// 客户渠道	
        /// </summary>
        public string customerchannel
        {
            get;
            set;
        }
        /// <summary>
        /// 营业部	
        /// </summary>
        public string salesdepartment
        {
            get;
            set;
        }
        /// <summary>
        /// 交易日期	
        /// </summary>
        public string transactiondate
        {
            get;
            set;
        }
        /// <summary>
        /// 购买产品	
        /// </summary>
        public string buyproduect
        {
            get;
            set;
        }
        /// <summary>
        /// 交易金额
        /// </summary>
        public decimal transactionamount
        {
            get;
            set;
        }

        /// <summary>
        /// 保存导入的市场营销反馈清单
        /// </summary>
        /// <param name="mfb"></param>
        /// <returns></returns>
        public int AddNewMarketingFeedback(MarketingFeedbackMana mfb)
        {
            string sql = "Insert into Tbt_MarketingFeedback(LISTPCNO,MARKETINGNAME,MARKETINGPERIOD,GUESTNO,CUSTOMERCHANNEL,"
                        + "SALESDEPARTMENT,TRANSACTIONDATE,BUYPRODUCT,TRANSACTIONAMOUNT) values('" + mfb.listpcno + "','"
                        + mfb.marketingname + "','" + mfb.marketingperiod + "','" + mfb.guestno + "','" + mfb.customerchannel + "','"
                        + mfb.salesdepartment + "','" + mfb.transactiondate + "','" + mfb.buyproduect + "',"
                        + mfb.transactionamount.ToString() + ")";
            sqldb db = new sqldb();
            return db.ExecuteNonQuery(sqlconn.GetConnStr(), CommandType.Text, sql);            
        }

    }

    /// <summary>
    /// 营销效果评估for营业部
    /// </summary>
    public class MarketingFeedbackSaleDepartment
    {
        //清单批次号
        /// <summary>
        /// 清单批次号
        /// </summary>
        public string listpcno
        {
            get;
            set;
        }
        /// <summary>
        /// 营销活动名称	
        /// </summary>
        public string marketingname
        {
            get;
            set;
        }
        /// <summary>
        /// 营业部
        /// </summary>
        public string saledepartment
        {
            get;
            set;
        }
        /// <summary>
        /// 下发名单	
        /// </summary>
        public int xfnamelist
        {
            get;
            set;
        }
        /// <summary>
        /// 签约人次
        /// </summary>
        public int singlecount
        {
            get;
            set;
        }
        /// <summary>
        /// 累计销售金额(万元)
        /// </summary>
        public decimal ljsaleamount
        {
            get;
            set;
        }
        /// <summary>
        /// 名单组签约人次
        /// </summary>
        public int namelistcount
        {
            get;
            set;
        }
        /// <summary>
        /// 名单组认购金额(万元)
        /// </summary>
        public decimal namelistamount
        {
            get;
            set;
        }
        /// <summary>
        /// 名单占比	
        /// </summary>
        public decimal namelistrate
        {
            get;
            set;
        }
        /// <summary>
        /// 名单认购金额占比
        /// </summary>
        public decimal amountrate
        {
            get;
            set;
        }
        /// <summary>
        /// 名单转化率
        /// </summary>
        public decimal namezhrate
        {
            get;
            set;
        }

        public IList<MarketingFeedbackSaleDepartment> GetFeedbackSaleDepartment(string param)
        {
            string sql = "Execute Proc_Marketingfeedback '" + param + "'";
            sqldb db = new sqldb();
            DataSet ds = db.ExecuteDataset(sqlconn.GetConnStr(), CommandType.Text, sql);
            IList<MarketingFeedbackSaleDepartment> marklist = null;
            if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
            {
                marklist = new List<MarketingFeedbackSaleDepartment>();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    MarketingFeedbackSaleDepartment markmana = new MarketingFeedbackSaleDepartment();
                    markmana.listpcno = dr["LISTPCNO"].ToString();
                    markmana.marketingname = dr["MARKETINGNAME"].ToString();
                    markmana.saledepartment = dr["SALESDEPARTMENT"].ToString();
                    markmana.xfnamelist = Convert.ToInt32(dr["XFMD"]);
                    markmana.singlecount = Convert.ToInt32(dr["QYRSH"]);
                    markmana.ljsaleamount = Convert.ToDecimal(dr["TRANSCSUM"]);
                    markmana.namelistcount = Convert.ToInt32(dr["NAMELIST"]);
                    markmana.namelistamount = Convert.ToDecimal(dr["NAMELISTSUM"]);
                    markmana.namelistrate = Convert.ToDecimal(dr["NAMEAMOUNTRATE"]);
                    markmana.amountrate = Convert.ToDecimal(dr["NAMEZH"]);
                    markmana.namezhrate = Convert.ToDecimal(dr["NAMEZH"]);
                    marklist.Add(markmana);
                }
            }
            return marklist;
        }
        /// <summary>
        /// 根据历史营销活动查询
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public IList<MarketingFeedbackSaleDepartment> GetHistoryFeedbackSaleDepartment(string param)
        {
            string sql = "select * from Tbt_MarketingSalesdepartmentGroup where MARKETINGNAME = '" + param + "'";
            sqldb db = new sqldb();
            DataSet ds = db.ExecuteDataset(sqlconn.GetConnStr(), CommandType.Text, sql);
            IList<MarketingFeedbackSaleDepartment> marklist = null;
            if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
            {
                marklist = new List<MarketingFeedbackSaleDepartment>();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    MarketingFeedbackSaleDepartment markmana = new MarketingFeedbackSaleDepartment();
                    markmana.listpcno = dr["LISTPCNO"].ToString();
                    markmana.marketingname = dr["MARKETINGNAME"].ToString();
                    markmana.saledepartment = dr["SALESDEPARTMENT"].ToString();
                    markmana.xfnamelist = Convert.ToInt32(dr["XFMD"]);
                    markmana.singlecount = Convert.ToInt32(dr["QYRSH"]);
                    markmana.ljsaleamount = Convert.ToDecimal(dr["TRANSCSUM"]);
                    markmana.namelistcount = Convert.ToInt32(dr["NAMELIST"]);
                    markmana.namelistamount = Convert.ToDecimal(dr["NAMELISTSUM"]);
                    markmana.namelistrate = Convert.ToDecimal(dr["NAMEAMOUNTRATE"]);
                    markmana.amountrate = Convert.ToDecimal(dr["NAMEZH"]);
                    markmana.namezhrate = Convert.ToDecimal(dr["NAMEZH"]);
                    marklist.Add(markmana);
                }
            }
            return marklist;
        }
    }

    /// <summary>
    /// 特选名单组与非特选名单组效果对比
    /// </summary>
    public class MarketingEffectEvaluation
    {
        /// <summary>
        /// 清单批次号
        /// </summary>
        public string listpcno
        {
            get;
            set;
        }
        /// <summary>
        /// 营销活动名称
        /// </summary>
	    public string marketingname
        {
            get;
            set;
        }
        /// <summary>
        /// 类型	
        /// </summary>
        public string marketingtype
        {
            get;
            set;
        }
        /// <summary>
        /// 客户数
        /// </summary>
		public int guestcount
        {
            get;
            set;
        }
        /// <summary>
        /// 认购客户数
        /// </summary>
		public int rgguestcount
        {
            get;
            set;
        }
        /// <summary>
        /// 认购金额	
        /// </summary>
		public decimal rgamount
        {
            get;
            set;
        }
        /// <summary>
        /// 人均认购金额
        /// </summary>
		public decimal rgamountper
        {
            get;
            set;
        }
        /// <summary>
        /// 认购转化率
        /// </summary>
        public decimal rgrate
        {
            get;
            set;
        }

        /// <summary>
        /// 购买转化率提升值 / 人均认购额差值
        /// </summary>
        public decimal otherrate
        {
            get;
            set;
        }
        public IList<MarketingEffectEvaluation> GetEffectEvaluation(string param)
        {
            string sql = "Execute Proc_MarketingEffectEvaluation '" + param + "'";
            sqldb db = new sqldb();
            DataSet ds = db.ExecuteDataset(sqlconn.GetConnStr(), CommandType.Text, sql);
            IList<MarketingEffectEvaluation> marklist = null;
            if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
            {
                marklist = new List<MarketingEffectEvaluation>();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    MarketingEffectEvaluation markmana = new MarketingEffectEvaluation();
                    markmana.listpcno = dr["LISTPCNO"].ToString();
                    markmana.marketingname = dr["MARKETINGNAME"].ToString();
                    markmana.marketingtype = dr["MARKETINGTYPE"].ToString();
                    markmana.guestcount = Convert.ToInt32(dr["GUESTCOUNT"]);
                    markmana.rgguestcount = Convert.ToInt32(dr["RGGUESTCOUNT"]);
                    markmana.rgamount = Convert.ToDecimal(dr["RGAMOUNT"]);
                    markmana.rgamountper = Convert.ToDecimal(dr["RGAMOUNTPER"]);
                    markmana.rgrate = Convert.ToDecimal(dr["RGRATE"]);
                    markmana.otherrate = Convert.ToDecimal(dr["OTHERRATE"]);
                    marklist.Add(markmana);
                }               
            }
            return marklist;
        }
        /// <summary>
        /// 根据历史活动名称查询
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public IList<MarketingEffectEvaluation> GetHistoryEffectEvaluation(string param)
        {
            string sql = "select * from Tbt_MarketingFeedbackGroup where MARKETINGNAME = '" + param + "'";
            sqldb db = new sqldb();
            DataSet ds = db.ExecuteDataset(sqlconn.GetConnStr(), CommandType.Text, sql);
            IList<MarketingEffectEvaluation> marklist = null;
            if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
            {
                marklist = new List<MarketingEffectEvaluation>();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    MarketingEffectEvaluation markmana = new MarketingEffectEvaluation();
                    markmana.listpcno = dr["LISTPCNO"].ToString();
                    markmana.marketingname = dr["MARKETINGNAME"].ToString();
                    markmana.marketingtype = dr["MARKETINGTYPE"].ToString();
                    markmana.guestcount = Convert.ToInt32(dr["GUESTCOUNT"]);
                    markmana.rgguestcount = Convert.ToInt32(dr["RGGUESTCOUNT"]);
                    markmana.rgamount = Convert.ToDecimal(dr["RGAMOUNT"]);
                    markmana.rgamountper = Convert.ToDecimal(dr["RGAMOUNTPER"]);
                    markmana.rgrate = Convert.ToDecimal(dr["RGRATE"]);
                    markmana.otherrate = Convert.ToDecimal(dr["OTHERRATE"]);
                    marklist.Add(markmana);
                }
            }
            return marklist;
        }
    }
}
